ALTER  proc sp_create_update_sort_lot (

   @PART_NUMBER 		VARCHAR(50), 
   @LOT_NUMBER 			VARCHAR(50), 
   @LOT_STATUS 			VARCHAR(50), 
   @WAFER_QUANTITY 		INT, 
   @PURCHASE_ORDER_NUMBER	VARCHAR(50),
   @DATE_IN 			DATETIME,
   @LOT_STAGE_NAME 		VARCHAR(50),
   @EXPECTED_DATE_OUT 		DATETIME,
   @VENDOR_NAME 		VARCHAR(50),
   @QUANTITY_OUT 		VARCHAR(50),
   @DATE_OUT 			DATETIME,
   @DIE_QTY                     VARCHAR(50),
   @FAB_LOTNUMBER		VARCHAR(50),   
   @HOLD_CODE			VARCHAR(50),
   @HOLD_REASON			VARCHAR(50),
   @HOLD_DATE			DATETIME,
   @TESTERNO			VARCHAR(50),
   @OWNER			VARCHAR(50),
   @YIELD			VARCHAR(50),
   @WORK_ORDER			VARCHAR(50),
   @GROSS_DIE			VARCHAR(50),
   @WAFER_ID			VARCHAR(150),
   @LOT_CATEGORY		VARCHAR(10),
   @FLOW			VARCHAR(50),
   @TEST_PROGRAM		VARCHAR(50), 
   @SHEET_DATE_IN               DATETIME 		
   ) 
   AS
   BEGIN
   	DECLARE @process_step_id 	INT
   	DECLARE @location_id 		INT
   	DECLARE @lot_id 		INT
   	DECLARE @this_lot_status	VARCHAR(50)
   	DECLARE @vendor_id 		INT
   	DECLARE @purchase_order_id 	INT
   	
	DECLARE @date_time 	DATETIME
   	DECLARE @start_date	DATETIME
   	DECLARE @end_date 	DATETIME
	DECLARE @LOT_DATEIN	DATETIME 
   		
   	if @LOT_CATEGORY is null
	begin
		set @LOT_CATEGORY = 'PROD'
	end 

   	SELECT @process_step_id = Process_Step.ProcessStep_ID FROM product,Process_Step WHERE product.part_number = @part_number AND PS_Type_Name = 'SORT' AND product.ProcessStep_ID = Process_Step.ProcessStep_ID 
   	SELECT @location_id = Location_ID FROM Location WHERE L_Type = 'Sort' AND L_Vendor_Name = @vendor_name
   	
   	IF @process_step_id IS NULL -- new Product or Product Name mismatch
   	BEGIN
   		SELECT @process_step_id = Process_Step.ProcessStep_ID FROM Product_Map_Table,Process_Step WHERE Product_Map_Table.part_number = @part_number AND PS_Type_Name='SORT' AND Product_Map_Table.ProcessStep_ID=Process_Step.ProcessStep_ID
   	
   		IF @process_step_id IS NULL
   		BEGIN
			--For Discrepancy Lot Notification Alert
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @fab_lotnumber AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = 'SORT' AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp)
					VALUES ( @part_number, @lot_number, @fab_lotnumber, @vendor_name, 'SORT','Discrepancy Lot Notification', @wafer_quantity, @Die_Qty, @wafer_Id, 'Open', CURRENT_TIMESTAMP)
			END
   			-- dummy update to avoid SQLException
   			UPDATE Lot_Table SET Lot_Number = 'Lot_Number' WHERE Lot_Number = 'Lot_Number'
   			RETURN
   		END
   	END
   	
   	SELECT @this_lot_status = Lot_Current_Status, @lot_id = Lot_ID,@LOT_DATEIN = DATE_IN FROM Lot_Table WHERE ProcessStep_ID = @process_step_id AND Location_ID = @location_id AND Lot_Number = @lot_number	
	
	IF @DATE_IN IS NULL AND @LOT_DATEIN IS NULL
    BEGIn
        SET @DATE_IN = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
    END
    ELSE
    BEGIN
         IF @DATE_IN IS NULL AND @LOT_DATEIN IS NOT NULL
		 BEGIN	
			SET @DATE_IN = @LOT_DATEIN
		 END
    END

   	IF @lot_id IS NULL -- Check if the lot exists
   	BEGIN -- Create Lot
   	
   		insert into lot_table (processstep_id, location_id, lot_number, fab_lot_number, vendor_lot_number, wafer_qty_in, date_in, date_out,stage_name, lot_current_status, timestamp,lot_entered_by, lot_category, Good_Die_Qty, Lot_Drop_Out, PO_Number) 
			values (@process_step_id, @location_id, @lot_number, @fab_lotnumber, @lot_number, @wafer_quantity, @date_in, @date_out, @lot_stage_name, @lot_status, current_timestamp, 1 , @lot_category, @Die_Qty, 0, @purchase_order_number )
   		SELECT @lot_id = @@IDENTITY

		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp )
				VALUES ( @part_number, @lot_number, @fab_lotnumber, @vendor_name, 'SORT','New Lot Notification', @wafer_quantity, @Die_Qty, @wafer_Id, 'Open', CURRENT_TIMESTAMP)		   	   		
		
   		-- Insert Sort Lot
   		INSERT INTO Sort_Lot (Lot_ID, SL_Expected_Date_Out, SL_Gross_Die, SL_Hold_Reason, SL_Hold_Code, SL_HOLD_Date, SL_Tester,  SL_Owner, SL_Wafer_Id, SL_WO_Order, SL_Yield, SL_Avg_Good_Die, SL_Flow, SL_Test_Program, TIMESTAMP) VALUES (@lot_id, @expected_date_out, @gross_die, @Hold_reason, @Hold_Code,  @Hold_Date, @TesterNo, @Owner, @wafer_id,  @work_order,  @yield, @Die_Qty, @Flow, @Test_Program, CURRENT_TIMESTAMP)
   	END
   	ELSE 
   	BEGIN
   		-- UPDATE LOT TABLE
   		update Lot_Table set Lot_Current_Status = @lot_status, Wafer_Qty_In = @wafer_quantity, Stage_Name = @lot_stage_name, Lot_Category = @lot_category, Good_Die_Qty = @Die_Qty, Lot_Drop_Out = 0 where Lot_ID = @lot_id
		UPDATE Sort_Lot SET SL_Expected_Date_Out=@expected_date_out, SL_Gross_Die = @gross_die, SL_Hold_Reason = @Hold_reason, SL_Hold_Code = @Hold_Code, SL_HOLD_Date = @Hold_Date, SL_Tester = ISNULL(@TesterNo,SL_Tester ), SL_Owner = @Owner, SL_Wafer_Id = @wafer_id, SL_WO_Order = @work_order, SL_Yield = ISNULL(@yield,SL_Yield), SL_Avg_Good_Die = @Die_Qty, SL_Flow = @Flow, SL_Test_Program = ISNULL(@Test_Program,SL_Test_Program), TIMESTAMP=CURRENT_TIMESTAMP WHERE Lot_ID = @lot_id   	
   	END
	
	/* Closing Transit Lots and Previous Process Lots*/
	Exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @lot_number, @fab_lotnumber, @wafer_quantity, @date_in
   	
   END

